package com.whsw.scheduler.trading_center_scheduler.scheduler.reportFormScheduler;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;

import java.sql.SQLException;
import java.util.List;

/**
 * 报市建局1定时器
 * @Author liangyiqiang 2021/03/19
 * */
@Component
public class HouseReportScheduler {
    @Autowired
    DSFactory dsFactory;

    //    @Scheduled(cron = "0/10 * * * * ?")
//    public void test() throws Exception {
//        Db db1 = Db.use(dsFactory.getDataSource("a"));
//        List<Entity> list = db1.query("" +
//                "SELECT" +
//                " create_time AS create_time," +
//                "COUNT(project_id) AS project_num," +
//                "SUM(IFNULL(bid_price, '0.00')) AS bid_price," +
//                "project_type AS project_type," +
//                "max_limit_bid_price AS max_limit_bid_price" +
//                " FROM" +
//                " construction_project_bid_month_summary" +
//                " WHERE" +
//                " YEAR(create_time) = 2020" +
//                " AND project_type=1" +
//                " AND flag=1" +
//                " GROUP BY MONTH(create_time)" +
//                " ORDER BY MONTH(create_time)");
//        for (Entity entity : list) {
//            System.out.println(entity);
//        }
//    }


    /**
     * 报市住建局1-房建市政项目排名情况
     * @Author liangyiqiang 2021/03/19
     * */
    @Scheduled(cron = "0/10 * * * * ?")
    public void getHouseConstructionMunicipalProjectsRank() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));
        Db db2 = Db.use(dsFactory.getDataSource("b"));
        List<Entity> list = db2.query(
                "select " +
                        "a.package_code AS project_id," +
                        "a.package_name AS project_name," +
                        "sum(d.price) AS transaction_price," +
                        "from pub_t_package a\n" +
                        "left join pub_t_project b on a.project_id = b.project_id \n" +
                        "left join pub_t_result_letter c on c.package_id = a.package_id \n" +
                        "left join gb_t_result_letter_enterprise d on d.result_letter_id =  c.result_letter_id \n" +
                        "where \n" +
                        "c.approval_status = 'passed'\n" +
                        "and c.status = 1 \n" +
                        "and b.industry in ('FJ','SZ')\n" +
                        "group by package_code");

        if(!ObjectUtils.isEmpty(list)){
            db1.del(
                    Entity.create("house_construction_municipal_projects_rank")
            );
            for (Entity entity : list) {
                entity.setTableName("house_construction_municipal_projects_rank");
            }
            db1.insert(list);
        }
    }


    /**
     * 报市住建局1-监理项目排名情况
     * @Author liangyiqiang 2021/03/19
     * */
    @Scheduled(cron = "0/10 * * * * ?")
    public void getSupervisionProjectRankInfo() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));
        Db db2 = Db.use(dsFactory.getDataSource("b"));
        List<Entity> list = db2.query("" +
                "select" +
                " a.package_code AS project_id," +
                "a.package_name AS project_name," +
                "d.price AS transaction_price," +
                "d.enterprise_name AS bid_win_enterprise," +
                "from pub_t_package a\n" +
                "left join pub_t_project b on a.project_id = b.project_id \n" +
                "left join pub_t_result_letter c on c.package_id = a.package_id \n" +
                "left join gb_t_result_letter_enterprise d on d.result_letter_id = c.result_letter_id \n" +
                "where \n" +
                "c.approval_status = 'passed'\n" +
                "and c.status = 1\n" +
                "and b.tender_category_detail in ('FW_JL','FW_JC')");
        if(!ObjectUtils.isEmpty(list)){
            db1.del(
                    Entity.create("supervision_project_rank")
            );
            for (Entity entity : list) {
                entity.setTableName("supervision_project_rank");
            }
            db1.insert(list);
        }
    }

    /**
     * 报市住建局1-检测项目排名情况
     * @Author liangyiqiang 2021/03/19
     * */
    public void getTestProjectRankInfo() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));
        Db db2 = Db.use(dsFactory.getDataSource("b"));
        List<Entity> list = db2.query("" +
                "select" +
                " a.package_code AS project_id," +
                "a.package_name AS project_name," +
                "d.price AS transaction_price," +
                "d.enterprise_name AS bid_win_enterprise," +
                "from pub_t_package a\n" +
                "left join pub_t_project b on a.project_id = b.project_id \n" +
                "left join pub_t_result_letter c on c.package_id = a.package_id \n" +
                "left join gb_t_result_letter_enterprise d on d.result_letter_id = c.result_letter_id \n" +
                "where \n" +
                "c.approval_status = 'passed'\n" +
                "and c.status = 1\n" +
                "and b.tender_category_detail in ('FW_JL','FW_JC')");
        if(!ObjectUtils.isEmpty(list)){
            db1.del(
                    Entity.create("test_project_rank")
            );
            for (Entity entity : list) {
                entity.setTableName("test_project_rank");
            }
            db1.insert(list);
        }
    }

}
